import 'dart:developer' as developer;
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

import '../repositories/word_repository.dart';
import '../repositories/question_repository.dart';
import '../repositories/note_repository.dart';
import '../repositories/practice_record_repository.dart';

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  factory DatabaseService() => _instance;
  DatabaseService._internal();
  
  // 添加表名常量
  static const String wordsTable = 'words';
  static const String questionsTable = 'questions';
  static const String notesTable = 'notes';
  static const String essayMaterialsTable = 'essay_materials';
  static const String checkInsTable = 'check_ins';
  static const String aiChatMessagesTable = 'ai_chat_messages';
  static const String practiceRecordsTable = 'practice_records';
  
  Database? _database;
  bool _initialized = false;
  
  // 添加 WordRepository 实例
  late WordRepository _wordRepository;
  WordRepository get wordRepository => _wordRepository;
  
  // 添加其他Repository的getter方法
  late QuestionRepository _questionRepository;
  QuestionRepository get questionRepository => _questionRepository;
  
  late NoteRepository _noteRepository;
  NoteRepository get noteRepository => _noteRepository;
  
  late PracticeRecordRepository _practiceRecordRepository;
  PracticeRecordRepository get practiceRecordRepository => _practiceRecordRepository;
  
  // 添加 init 方法作为 initDatabase 的别名
  Future<void> init() async {
    await initDatabase();
  }
  
  // 添加一个标志来跟踪初始化过程
  bool _isInitializing = false;
  
  Future<void> initDatabase() async {
    // 如果已经初始化完成，直接返回
    if (_initialized) {
      developer.log('DatabaseService: 数据库已初始化');
      return;
    }
    
    // 如果正在初始化中，等待初始化完成
    if (_isInitializing) {
      developer.log('DatabaseService: 数据库正在初始化中，等待完成');
      // 等待一段时间后再检查初始化状态
      await Future.delayed(const Duration(milliseconds: 100));
      if (!_initialized) {
        return initDatabase(); // 递归调用，直到初始化完成
      }
      return;
    }
    
    _isInitializing = true;
    
    try {
      developer.log('DatabaseService: 初始化桌面/移动平台数据库');
      final databasesPath = await getDatabasesPath();
      final path = join(databasesPath, 'study_treasure.db');
      
      _database = await openDatabase(
        path,
        version: 8, // 增加版本号，触发数据库升级
        onCreate: _createDatabase,
        onUpgrade: _upgradeDatabase,
      );
      
      developer.log('DatabaseService: SQLite数据库初始化完成，路径: $path');
      
      _wordRepository = WordRepository();
      _questionRepository = QuestionRepository();
      _noteRepository = NoteRepository();
      _practiceRecordRepository = PracticeRecordRepository();
      
      _initialized = true;
      _isInitializing = false;
      
      developer.log('数据库服务初始化完成');
    } catch (e) {
      _isInitializing = false;
      developer.log('数据库服务初始化失败: $e', error: e);
      rethrow;
    }
  }
  
  Future<void> _createDatabase(Database db, int version) async {
    developer.log('DatabaseService: 创建SQLite数据库表');
    
    // 创建单词表
    await db.execute('''
      CREATE TABLE ${DatabaseService.wordsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        word TEXT NOT NULL,
        translation TEXT,
        pronunciation TEXT,
        meaning TEXT,
        example TEXT,
        note TEXT,
        category TEXT,
        difficulty INTEGER DEFAULT 1,
        createdAt INTEGER NOT NULL,
        lastReviewedAt INTEGER,
        memoryStrength REAL DEFAULT 0.0
      )
    ''');
    
    // 创建问题表
    await db.execute('''
      CREATE TABLE ${DatabaseService.questionsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        question TEXT NOT NULL,
        option_a TEXT NOT NULL,
        option_b TEXT NOT NULL,
        option_c TEXT NOT NULL,
        option_d TEXT NOT NULL,
        correct_answer TEXT NOT NULL,
        explanation TEXT,
        category TEXT,
        key_point TEXT DEFAULT "",
        difficulty INTEGER DEFAULT 1,
        tags TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        type TEXT DEFAULT "common_sense",
        question_image_url TEXT,
        option_a_image_url TEXT,
        option_b_image_url TEXT,
        option_c_image_url TEXT,
        option_d_image_url TEXT,
        explanation_image_url TEXT
      )
    ''');
    
    // 创建笔记表
    await db.execute('''
      CREATE TABLE ${DatabaseService.notesTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        category TEXT,
        tags TEXT,
        image_url TEXT,
        rich_content TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');
    
    // 创建作文素材表
    await db.execute('''
      CREATE TABLE ${DatabaseService.essayMaterialsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        category TEXT,
        createdAt INTEGER NOT NULL
      )
    ''');
    
    // 创建签到表
    await db.execute('''
      CREATE TABLE ${DatabaseService.checkInsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT UNIQUE NOT NULL,
        streak INTEGER DEFAULT 1
      )
    ''');
    
    // 创建AI聊天消息表
    await db.execute('''
      CREATE TABLE ${DatabaseService.aiChatMessagesTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        role TEXT NOT NULL,
        content TEXT NOT NULL,
        timestamp INTEGER NOT NULL
      )
    ''');
    
    // 创建练习记录表
    await db.execute('''
      CREATE TABLE ${DatabaseService.practiceRecordsTable} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        questionId INTEGER NOT NULL,
        userAnswer TEXT NOT NULL,
        isCorrect INTEGER NOT NULL,
        date INTEGER NOT NULL,
        FOREIGN KEY (questionId) REFERENCES ${DatabaseService.questionsTable} (id) ON DELETE CASCADE
      )
    ''');
    
    developer.log('DatabaseService: 所有表创建完成');
  }
  
  Future<void> _upgradeDatabase(Database db, int oldVersion, int newVersion) async {
    developer.log('DatabaseService: 升级数据库，版本: $oldVersion -> $newVersion');
    
    // 版本7升级到版本8：为笔记表添加rich_content字段
    if (oldVersion <= 7 && newVersion >= 8) {
      developer.log('DatabaseService: 升级数据库 - 为笔记表添加rich_content字段');
      
      try {
        await db.execute('ALTER TABLE ${DatabaseService.notesTable} ADD COLUMN rich_content TEXT');
        developer.log('DatabaseService: rich_content列添加完成');
      } catch (e) {
        developer.log('DatabaseService: 添加rich_content列时出错，可能列已存在: $e');
      }
    }
    
    // 版本6升级到版本7：为questions表添加图片路径字段
    if (oldVersion <= 6 && newVersion >= 7) {
      developer.log('DatabaseService: 升级数据库 - 为questions表添加图片路径字段');
      
      // 添加图片路径字段
      try {
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN question_image_url TEXT');
        developer.log('DatabaseService: question_image_url列添加完成');
      } catch (e) {
        developer.log('DatabaseService: 添加question_image_url列时出错，可能列已存在: $e');
      }
      
      try {
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN option_a_image_url TEXT');
        developer.log('DatabaseService: option_a_image_url列添加完成');
      } catch (e) {
        developer.log('DatabaseService: 添加option_a_image_url列时出错，可能列已存在: $e');
      }
      
      try {
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN option_b_image_url TEXT');
        developer.log('DatabaseService: option_b_image_url列添加完成');
      } catch (e) {
        developer.log('DatabaseService: 添加option_b_image_url列时出错，可能列已存在: $e');
      }
      
      try {
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN option_c_image_url TEXT');
        developer.log('DatabaseService: option_c_image_url列添加完成');
      } catch (e) {
        developer.log('DatabaseService: 添加option_c_image_url列时出错，可能列已存在: $e');
      }
      
      try {
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN option_d_image_url TEXT');
        developer.log('DatabaseService: option_d_image_url列添加完成');
      } catch (e) {
        developer.log('DatabaseService: 添加option_d_image_url列时出错，可能列已存在: $e');
      }
      
      try {
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN explanation_image_url TEXT');
        developer.log('DatabaseService: explanation_image_url列添加完成');
      } catch (e) {
        developer.log('DatabaseService: 添加explanation_image_url列时出错，可能列已存在: $e');
      }
    }
    
    // 版本5升级到版本6：为questions表添加type字段
    if (oldVersion <= 5 && newVersion >= 6) {
      developer.log('DatabaseService: 升级数据库 - 为questions表添加type字段');
      
      try {
        // 添加type列
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN type TEXT DEFAULT "common_sense"');
        developer.log('DatabaseService: type列添加完成');
      } catch (e) {
        // 如果列已存在，SQLite会抛出错误，我们可以忽略它
        developer.log('DatabaseService: 添加type列时出错，可能列已存在: $e');
      }
    }
    
    // 版本1升级到版本2：重新创建问题表
    if (oldVersion == 1 && newVersion >= 2) {
      developer.log('DatabaseService: 升级数据库 - 重新创建问题表');
      
      // 先删除旧表
      await db.execute('DROP TABLE IF EXISTS ${DatabaseService.questionsTable}');
      
      // 创建新的问题表
      await db.execute('''
        CREATE TABLE ${DatabaseService.questionsTable} (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          question TEXT NOT NULL,
          option_a TEXT NOT NULL,
          option_b TEXT NOT NULL,
          option_c TEXT NOT NULL,
          option_d TEXT NOT NULL,
          correct_answer TEXT NOT NULL,
          explanation TEXT,
          category TEXT,
          key_point TEXT DEFAULT "",
          difficulty INTEGER DEFAULT 1,
          tags TEXT,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL,
          type TEXT DEFAULT "common_sense"
        )
      ''');
      
      developer.log('DatabaseService: 问题表升级完成');
    }
    
    // 版本2升级到版本3：添加key_point列
    if ((oldVersion == 2 && newVersion >= 3) || (oldVersion == 1 && newVersion == 3)) {
      developer.log('DatabaseService: 升级数据库 - 添加key_point列');
      
      try {
        // 添加key_point列
        await db.execute('ALTER TABLE ${DatabaseService.questionsTable} ADD COLUMN key_point TEXT DEFAULT ""');
        developer.log('DatabaseService: key_point列添加完成');
      } catch (e) {
        // 如果列已存在，SQLite会抛出错误，我们可以忽略它
        developer.log('DatabaseService: 添加key_point列时出错，可能列已存在: $e');
      }
    }
    
    // 版本3升级到版本4：为单词表添加pronunciation和meaning字段，为笔记表添加tags和image_url字段
    if (oldVersion <= 3 && newVersion >= 4) {
      developer.log('DatabaseService: 升级数据库 - 为单词表添加pronunciation和meaning字段');
      
      try {
        // 添加pronunciation列
        await db.execute('ALTER TABLE ${DatabaseService.wordsTable} ADD COLUMN pronunciation TEXT');
        developer.log('DatabaseService: pronunciation列添加完成');
      } catch (e) {
        // 如果列已存在，SQLite会抛出错误，我们可以忽略它
        developer.log('DatabaseService: 添加pronunciation列时出错，可能列已存在: $e');
      }
      
      try {
        // 添加meaning列
        await db.execute('ALTER TABLE ${DatabaseService.wordsTable} ADD COLUMN meaning TEXT');
        developer.log('DatabaseService: meaning列添加完成');
      } catch (e) {
        // 如果列已存在，SQLite会抛出错误，我们可以忽略它
        developer.log('DatabaseService: 添加meaning列时出错，可能列已存在: $e');
      }
      
      developer.log('DatabaseService: 升级数据库 - 为笔记表添加tags和image_url字段');
      
      try {
        // 添加tags列
        await db.execute('ALTER TABLE ${DatabaseService.notesTable} ADD COLUMN tags TEXT');
        developer.log('DatabaseService: tags列添加完成');
      } catch (e) {
        // 如果列已存在，SQLite会抛出错误，我们可以忽略它
        developer.log('DatabaseService: 添加tags列时出错，可能列已存在: $e');
      }
      
      try {
        // 添加image_url列
        await db.execute('ALTER TABLE ${DatabaseService.notesTable} ADD COLUMN image_url TEXT');
        developer.log('DatabaseService: image_url列添加完成');
      } catch (e) {
        // 如果列已存在，SQLite会抛出错误，我们可以忽略它
        developer.log('DatabaseService: 添加image_url列时出错，可能列已存在: $e');
      }
    }
    
    // 版本4升级到版本5：修复笔记表中的字段名，从驼峰命名法改为下划线命名法
    if (oldVersion <= 4 && newVersion >= 5) {
      developer.log('DatabaseService: 升级数据库 - 修复笔记表中的字段名');
      
      try {
        // 创建新的笔记表，使用下划线命名法
        await db.execute('''
          CREATE TABLE ${DatabaseService.notesTable}_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            category TEXT,
            tags TEXT,
            image_url TEXT,
            created_at INTEGER NOT NULL,
            updated_at INTEGER NOT NULL
          )
        ''');
        
        // 尝试从旧表复制数据到新表，处理可能的字段名差异
        try {
          // 先尝试使用驼峰命名法的字段名
          await db.execute('''
            INSERT INTO ${DatabaseService.notesTable}_new 
            SELECT id, title, content, category, tags, image_url, createdAt, updatedAt 
            FROM ${DatabaseService.notesTable}
          ''');
          developer.log('DatabaseService: 使用驼峰命名法字段名复制数据成功');
        } catch (e) {
          developer.log('DatabaseService: 使用驼峰命名法字段名复制数据失败: $e');
          
          try {
            // 如果失败，尝试使用下划线命名法的字段名
            await db.execute('''
              INSERT INTO ${DatabaseService.notesTable}_new 
              SELECT id, title, content, category, tags, image_url, created_at, updated_at 
              FROM ${DatabaseService.notesTable}
            ''');
            developer.log('DatabaseService: 使用下划线命名法字段名复制数据成功');
          } catch (e) {
            developer.log('DatabaseService: 复制数据失败: $e');
          }
        }
        
        // 删除旧表
        await db.execute('DROP TABLE ${DatabaseService.notesTable}');
        
        // 重命名新表
        await db.execute('ALTER TABLE ${DatabaseService.notesTable}_new RENAME TO ${DatabaseService.notesTable}');
        
        developer.log('DatabaseService: 笔记表字段名修复完成');
      } catch (e) {
        developer.log('DatabaseService: 修复笔记表字段名时出错: $e', error: e);
      }
    }
  }
  
  // 通用数据库操作方法
  
  Future<int> insert(String table, Map<String, dynamic> data) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 插入数据到表 $table');
      return await _database!.insert(table, data);
    } catch (e) {
      developer.log('DatabaseService: 插入数据失败: $e', error: e);
      rethrow;
    }
  }
  
  Future<List<Map<String, dynamic>>> getAll(String table) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 获取表 $table 的所有数据');
      return await _database!.query(table);
    } catch (e) {
      developer.log('DatabaseService: 获取所有数据失败: $e', error: e);
      return [];
    }
  }
  
  Future<Map<String, dynamic>?> getById(String table, int id) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 通过ID获取数据，表: $table，ID: $id');
      
      final results = await _database!.query(
        table,
        where: 'id = ?',
        whereArgs: [id],
        limit: 1,
      );
      return results.isNotEmpty ? results.first : null;
    } catch (e) {
      developer.log('DatabaseService: 通过ID获取数据失败: $e', error: e);
      return null;
    }
  }
  
  Future<List<Map<String, dynamic>>> getByField(String table, String field, dynamic value) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 通过字段获取数据，表: $table，字段: $field，值: $value');
      
      return await _database!.query(
        table,
        where: '$field = ?',
        whereArgs: [value],
      );
    } catch (e) {
      developer.log('DatabaseService: 通过字段获取数据失败: $e', error: e);
      return [];
    }
  }
  
  Future<bool> update(String table, Map<String, dynamic> data) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 更新数据，表: $table');
      
      if (!data.containsKey('id') || data['id'] == null) {
        developer.log('DatabaseService: 更新失败，缺少ID字段');
        return false;
      }
      
      final id = data['id'];
      final result = await _database!.update(
        table,
        data,
        where: 'id = ?',
        whereArgs: [id],
      );
      return result > 0;
    } catch (e) {
      developer.log('DatabaseService: 更新数据失败: $e', error: e);
      return false;
    }
  }
  
  Future<bool> delete(String table, int id) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 删除数据，表: $table，ID: $id');
      
      final result = await _database!.delete(
        table,
        where: 'id = ?',
        whereArgs: [id],
      );
      return result > 0;
    } catch (e) {
      developer.log('DatabaseService: 删除数据失败: $e', error: e);
      return false;
    }
  }
  
  Future<bool> clearTable(String table) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 清空表 $table');
      
      await _database!.delete(table);
      return true;
    } catch (e) {
      developer.log('DatabaseService: 清空表失败: $e', error: e);
      return false;
    }
  }
  
  Future<int> count(String table) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 获取表 $table 的记录数');
      
      final result = await _database!.rawQuery('SELECT COUNT(*) as count FROM $table');
      return Sqflite.firstIntValue(result) ?? 0;
    } catch (e) {
      developer.log('DatabaseService: 获取记录数失败: $e', error: e);
      return 0;
    }
  }
  
  /// 删除表中所有数据
  Future<bool> deleteAll(String table) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 删除表 $table 中所有数据');
      
      await _database!.delete(table);
      return true;
    } catch (e) {
      developer.log('DatabaseService: 删除所有数据失败: $e', error: e);
      return false;
    }
  }
  
  // 执行原始SQL查询
  Future<List<Map<String, dynamic>>> rawQuery(String sql, [List<dynamic>? arguments]) async {
    await _ensureInitialized();
    
    try {
      developer.log('DatabaseService: 执行原始SQL查询: $sql');
      return await _database!.rawQuery(sql, arguments);
    } catch (e) {
      developer.log('DatabaseService: 执行原始SQL查询失败: $e', error: e);
      rethrow;
    }
  }
  
  Future<void> _ensureInitialized() async {
    if (!_initialized) {
      developer.log('DatabaseService: 数据库未初始化，正在初始化');
      await initDatabase();
    }
  }
  
  Future<void> close() async {
    developer.log('DatabaseService: 关闭数据库');
    
    await _database?.close();
    
    _database = null;
    _initialized = false;
    
    developer.log('DatabaseService: 数据库已关闭');
  }
}